#include "database.hpp"

namespace database
{
    sql_client::sql_client()
        : _driver_ptr(nullptr)
        , _con_ptr(nullptr)
    {}

    sql_client::~sql_client()
    {
        delete _con_ptr;
    }

    void sql_client::connect()
    {
        _driver_ptr = get_driver_instance();
        _con_ptr = _driver_ptr->connect("tcp://localhost:3306", "root", "minecraftCp0");
        _con_ptr->setSchema("medicine");
    }

    std::string sql_client::select()
    {
        std::unordered_map<size_t, std::string> get_warehouse_name;
        std::unordered_map<size_t, std::pair<size_t, size_t>> get_temp_range;
        std::unordered_map<size_t, std::pair<size_t, size_t>> get_humi_range;
        sql::Statement* stmt;
        sql::ResultSet* res;
        Json::Value ret;
        Json::Value diary;
        Json::FastWriter writer;
        stmt = _con_ptr->createStatement();
        std::string query = "SELECT w.id, w.name FROM warehouse w";
        res = stmt->executeQuery(query);
        while (res->next())
        {
            get_warehouse_name[res->getInt("id")] = res->getString("name");
        }
        delete res;
        query = "SELECT d.warehouse_id, d.min_temp, d.max_temp, d.min_humi, d.max_humi FROM device d";
        res = stmt->executeQuery(query);
        while (res->next())
        {
            get_temp_range[res->getInt("warehouse_id")] = std::make_pair(res->getInt("min_temp"), res->getInt("max_temp"));
            get_humi_range[res->getInt("warehouse_id")] = std::make_pair(res->getInt("min_humi"), res->getInt("max_humi"));
        }
        delete res;
        query = "SELECT warehouse.id, device.device_code, history.temp_data, history.humi_data, history.create_date "
                            "FROM history "
                            "INNER JOIN device ON history.device_id = device.id "
                            "INNER JOIN warehouse ON device.warehouse_id = warehouse.id";
        res = stmt->executeQuery(query);
        while (res->next()) 
        {
            size_t id = res->getInt("id");
            size_t temp_data = res->getInt("temp_data");
            size_t humi_data = res->getInt("humi_data");
            diary["warehouse_name"] = get_warehouse_name[id];
            diary["device_code"] = static_cast<std::string>(res->getString("device_code"));
            diary["temp_data"] = temp_data;
            diary["humi_data"] = humi_data;
            diary["create_date"] = static_cast<std::string>(res->getString("create_date"));
            diary["abnormal_temp"] = temp_data >= get_temp_range[id].first && temp_data <= get_temp_range[id].second;
            diary["abnormal_humi"] = humi_data >= get_humi_range[id].first && humi_data <= get_humi_range[id].second;
            ret["diary"].append(diary);
        }
        delete res;
        delete stmt;
        return writer.write(ret);
    }
}